当前位置：紫竹林-程序员中文网 > 科技 > 正文

并发丢数据深度剖析：MySQL锁机制与事务实战踩坑及解决方案

2025-11-12 分类：科技 阅读(8) 评论(0) 


                                                                                                                                                <span id="OSC_h1_1"></span>

1、理论来源于实践

现象 ：于 2025-08-13 21:45:35， 事实逻辑表将自身的指标与维度同步到原子服务的实现时，出现同步过来的指标与维度丢失。

核心原因 ：两次重复的事实逻辑表同步时间非常相近，导致同步过来的指标与维度丢失。





 

 

2、倒带进事故现场

逻辑表向原子服务同步的核心逻辑是 “先删后增”：删除旧数据→对比新老数据→插入新增数据，具体流程如下 ：

 



 

 

 

整体业务代码精简逻辑如下：

 

<span><span><span>@Transactional(rollbackFor = Exception.class)
public Map<String, Object> driveToAtomService(Map logicTableData, String erp) {
//获得环境信息
String env = driveLogicTable.getString(DRIVE_LOGIC_TABLE_ENV);
//获取/更新实现id
 Long logicTableId = getOrAddLogicTableId(atomicServiceId, driveLogicTable, erp, EnvType.of(env));
//删除关联指标
 metricImplMapper.deleteByLogicTableIds(Collections.singletonList(logicId));
//获取请求中的所有的指标信息
List<MetricImplBO> metricList = getMetricImpls(logicTableData, logicTableId);
//获取需要新增的指标实现(包含了查询库里现有的指标实现)
List<MetricImplRelBO> metricImpls = metricImplMapper.getMetricImpls(logicTableId);
Set<Long> metricDefIdSet = metricImpls.stream()
            .map(MetricImplRelBO::getMetricDefId).collect(Collectors.toSet());
List<MetricImplBO> addList = metricList.stream()
            .filter(s -> !metricDefIdSet.contains(s.getMetricDefId())).collect(Collectors.toList());
//将需要新增的指标实现插入数据库
 addMetricImpl(addList);

}

</span></span></span>

用一个请求进行举例：

<span><span><span>{"header":{"appKey":null,"uuid":"ce7cef2d-c417-464a-a519-311599fddfca","serviceName":"driveToAtomService","context":{"PIN":"wanyue3"}},"body":{"dimList":[{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":72,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2501,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2484,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2502,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4591,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3822,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4523,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4524,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":76,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1767,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1907,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1598,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4620,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4621,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4622,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2504,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2485,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2486,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2487,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2488,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3077,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3080,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3081,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2483,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2482,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3082,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3083,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4851,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2503,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5070,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5044,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5087,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5144,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5145,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3089,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3680,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2223,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5428,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5101,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1315,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5247,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3318,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5262,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4646,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2252,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2254,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2959,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2958,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2728,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2618,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5061,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6032,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6375,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6388,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6389,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1316,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1081,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1351,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1082,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1499,"type":"COMBINE"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1596,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1606,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1083,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1108,"type":"FILTER"}],"dimCombineList":[],"metricList":[{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19872,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19873,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19875,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19945,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":17263,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"COUNT_DISTINCT","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":28017,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":20242,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18450,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":20276,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18452,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18453,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18456,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19866,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":21691,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19871,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"}],"driveLogicTable":"{\"dimensionType\":\"DETAIL\",\"oldNameCn\":\"七鲜实时交易_for地推中间态新老标志\",\"atomicAliasProd\":\"prod\",\"implServiceTypeKey\":\"realtime\",\"originPhysicDataSourceId\":0,\"nameCn\":\"七鲜实时交易_for地推中间态新老标志\",\"description\":\"七鲜实时交易_for地推中间态新老标志\",\"driveLogicTableId\":9881,\"driveLogicTableEnv\":\"DEV\",\"commonDecorateIdList\":\"9665,3269,3270,3271,4556,8012,8270,6030,7247,6031,7248,6032,7249,6033,7250,6034,6035,2134,7254,7255,2085,619,620,5997,1586,7867,6845\",\"atomicAliasPre\":\"pre\",\"committer\":\"panjingrong\",\"physicDataSourceId\":9494,\"storageType\":\"ONLINE\",\"atomicAliasDev\":\"pre\"}","atomicServiceId":1088},"pin":"wanyue3"}

</span></span></span>

共计15个指标，64个维度

请求1(事务) 请求2(事务) 
21:06:17.262 进入同步方法 21:06:17.263 进入同步方法 
21:06:17.063 select unify_metric_impl where logic_id = 3245 查询出15条数据(快照读，readview1) 21:06:17.363 select unify_metric_impl where logic_id = 3245 查询出15条数据(快照读，readview2) 
21:06:17.363 delete from unify_metric_impl where logic_id = 3245 21:06:17.372 delete from unify_metric_impl where logic_id = 3245 
21:06:17.459 select unify_metric_impl where logic_id = 3245 查询出0条数据  delete 由于logic_id不是索引，会表锁阻塞 
21:06:18.459 insert into unify_metric_impl 插入的logic_id = 3245的数据，15条   
21:06:19.408 方法结束   
  21:06:19.529 删除成功 
  21:06:20.362 select unify_metric_impl where logic_id = 3245 得到 15条数据 
  21:06:20.435 读出15条数据，比较本次是否有新增指标， 得出没有新增指标，因此 不进行新增 。addAtomicMetricNameForDrive addList empty 
  21:06:21.435 方法结束 



核心结论点 ：

1.请求2的删除操作被阻塞了，直到请求1执行完整个方法。

2.请求2中去查看当前实现的指标的时候，发现库里已经存在所有指标不会进行新增，与上一步删除的逻辑相悖。



3、结论点深度剖析

3.1 分析结论一

请求2的删除操作被阻塞了，直到请求1执行完整个方法。

3.1.1 复习mysql的InnoDB锁机制

3.1.1.1 不是“一把锁”，而是 “锁矩阵”

锁粒度 共享锁（S 锁） （读锁，允许多读） 排他锁（X 锁） （写锁，独占） 意向锁（表级，辅助判断）
表级 表 S 锁（极少用，如 <span>LOCK TABLES ... READ</span> ） 表 X 锁（极少用，如 <span>LOCK TABLES ... WRITE</span> ） 意向 S 锁（IS）、意向 X 锁（IX）
行级 行 S 锁（ <span>SELECT ... FOR SHARE</span> ） 行 X 锁（ <span>UPDATE/DELETE/INSERT</span> 默认加） （行锁无需意向锁）
间隙级 间隙 S 锁（无，间隙只防插入） 间隙 X 锁（防其他事务插入相同间隙） 无
Next-Key 无 Next-Key 锁（行锁 + 间隙锁，默认行锁算法） 无



3.1.1.2 一张图总结：InnoDB 锁的 “决策逻辑”





 

 



3.1.2 理论应用实践

3.1.2.1 本次事故的物料：

mysql表：

<span><span><span>CREATE TABLE `unify_metric_impl` (
  `id` bigint(50) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `metric_def_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '指标定义id',
  `logic_table_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑表id',
  `name_en_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '真实指标名',
  `committer` varchar(64) NOT NULL DEFAULT '' COMMENT '负责人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `metric_atomic_name_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '原子指标id',
  `decorate_id_list_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '修饰列表',
  `name_cn_alias_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '中文别名',
  `metric_type_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '指标类型：DERIVE 衍生指标，FORMULA 复合指标',
  `description_temp` varchar(64) NOT NULL DEFAULT '' COMMENT '指标说明',
  `data_type_temp` varchar(16) NOT NULL DEFAULT '' COMMENT '数据类型：STRING，DOUBLE, LONG, INT',
  `data_accuracy_temp` tinyint(4) NOT NULL DEFAULT '2' COMMENT '数据精度-小数点后几位',
  `security_level_temp` varchar(16) DEFAULT '-1' COMMENT '安全等级',
  `logic_table_id_excel_temp` varchar(16) DEFAULT '-1' COMMENT '模型excelId',
  `implement_type` varchar(32) NOT NULL DEFAULT '' COMMENT '指标实现类型：APP、ATOMIC 原子服务',
  `app_ori_metric_name_temp` varchar(16) DEFAULT '' COMMENT '所依赖的app层原始名字（适用于导数任务改变字段的情况）',
  `name_en_depend_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '指标依赖字段',
  `name_en_depend_app` varchar(16) DEFAULT '' COMMENT '所依赖的app层原始名字（适用于导数任务改变字段的情况）',
  `update_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新状态 0-未完成更新，1-完成更新',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新状态 0-未完成更新，1-完成更新',
  `light_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '点灯修饰id列表',
  `extend_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的动态修饰id列表',
  `extend_function_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的原子服务函数id列表',
  `aggregation_type` varchar(64) NOT NULL DEFAULT '' COMMENT '聚合类型:ORIGINAL 原值 COUNT 计数 DISTINCT 指定字段去重 SUM 求和 AVG  均值 MIN 求最大值 MAX 求最小值 QUANTITLE 求分位数',
  `middle_aggregation_type` varchar(30) NOT NULL DEFAULT '' COMMENT '中间层类型,UNKNOWN:未知,AGG_BY_FIELD:按聚合字段分组后聚合,AGG_BY_DAY:按天去重后累加',
  `static_decorate_id_list_combination` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的固化修饰id列表组合,[[d1,d2],[d2]]',
  PRIMARY KEY (`id`),
  KEY `idx_metric_def_id` (`metric_def_id`,`logic_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='指标实现';

</span></span></span>

3.1.2.2 实践分析

通过mysql的innoDB的锁决策，可以得出

<span><span><span>//删除关联指标
 metricImplMapper.deleteByLogicTableIds(Collections.singletonList(logicId));

</span></span></span>
<span><span><span>delete from unify_metric_impl where logic_table_id in (45631);

</span></span></span>

mysql的索引：KEY `idx_metric_def_id` (`metric_def_id`,`logic_table_id`)

删除写操作，不符合最左匹配原则，因此为 表x锁 。



 

 

因此请求2的删除操作需要等待请求1的事务释放表锁后才可继续进行，符合当时场景。



3.2 分析结论二

请求2中去查看当前实现的指标的时候，发现库里已经存在所有指标不会进行新增，与上一步删除的逻辑相悖。

3.2.1 复习Mysql的事务

3.2.1.1 ACID 不是 “四个独立特性”，而是 “因果链”





 

 

• 一句话 ：ACID 的核心是 一致性 ，其他三个特性都是为了实现它的手段。
• 一致性 （Consistency）： 一致性确保事务将数据库从一个一致的状态转变到另一个一致的状态。即使在多个事务同时执行的情况下，数据库也能保持数据的一致性。
• 原子性 （Atomicity）：事务是 “不可分割的工作单元”（要么全成，要么全败），是一致性的 前提 （如果步骤能拆分，中间失败就会破坏一致性）。
• 隔离性 （Isolation）：通过控制多事务并发规则，避免互相干扰，是一致性的 保障 （并发混乱会直接破坏一致性）。
• 耐久性 （Durability）：事务提交后结果永久保存，是一致性的 最终落点 （否则重启后数据丢失，之前的一致性白搭）。



3.2.1.2 隔离级别：不是 “越严越好”，而是 “成本与需求的平衡术”

InnoDB 的 4 种隔离级别，本质是用 “数据可见性” 换 “并发性能”的选择：

隔离级别 解决的问题 无法解决的问题 性能消耗 典型场景
读未提交（RU） 无 脏读、不可重复读、幻读 极低 实时监控（允许脏数据）
读已提交（RC） 脏读 不可重复读、幻读 低 互联网普通业务
可重复读（RR，默认） 脏读、不可重复读 幻读（被 Next-Key 锁解决） 中 金融交易、库存管理
串行化（Serializable） 所有并发问题 无 极高 银行对账（无并发需求）



3.2.1.3 MVCC：事务的 “平行宇宙” 机制（为什么读写不冲突？）

InnoDB 的 多版本并发控制 是 “无锁读” 的核心，它让读和写像在平行宇宙中运行：

底层逻辑（用 “时间戳” 理解 ）：

• 每个事务启动时，会拿到一个 全局递增的事务 ID（trx_id） 。
• 每行数据隐藏 3 个字段：
◦ <span>DB_TRX_ID</span> ：最后修改该行的事务 ID；
◦ <span>DB_ROLL_PTR</span> ：指向 undo 日志的指针（存储历史版本）；
◦ <span>DB_DELETED</span> ：标记是否删除（逻辑删除）。

读操作的 “幻术” ：

• 快照读 （普通 SELECT）：只看 “事务 ID ≤ 自己 ID” 且 “未被删除” 的版本，完全不加锁。 例：事务 A（ID=100）查询时，会忽略所有被 ID>100 的事务修改的数据。

包含 4 个核心字段：

• <span>m_ids</span> ：生成 Read View 时， 当前活跃的事务 ID 列表 （未提交的事务）。
• <span>min_trx_id</span> ： <span>m_ids</span> 中最小的事务 ID。
• <span>max_trx_id</span> ：下一个将要分配的事务 ID（非活跃事务 ID，仅用于判断 “未来事务”）。
• <span>creator_trx_id</span> ：生成该 Read View 的事务自身 ID。

可见性判断规则 （一条记录是否对当前事务可见，取决于其 “最后修改事务 ID”，记为 <span>db_trx_id</span> ）：

1. 若db_trx_id == creator_trx_id：可见（自己修改的自己可见）。
2. 若db_trx_id < min_trx_id：可见（修改记录的事务在当前快照生成前已提交）。
3. 若db_trx_id >= max_trx_id：不可见（修改记录的事务在当前快照生成后才启动）。
4. 若min_trx_id ≤ db_trx_id < max_trx_id：
◦ 若db_trx_id在m_ids中：不可见（该事务仍活跃，未提交）。
◦ 若db_trx_id不在m_ids中：可见（该事务已提交）。
5. 当前读（加锁读 / 写操作）：读取最新版本，并加锁防止其他事务修改。

3.2.1.4 事务日志：InnoDB 的 “安全与性能” 平衡术

事务能既保证 durability 又不慢，全靠两大日志：

1. redo log（重做日志） ：
◦ 作用：崩溃后恢复未写入磁盘的数据（保证 durability）。
◦ 反直觉：事务提交时，数据先写 redo log（内存 + 磁盘），再异步刷到数据文件（这叫 WAL 技术）。
◦ 为什么快？redo log 是 顺序写 （磁盘顺序写比随机写快 100 倍 +）。
2. undo log（回滚日志） ：
◦ 作用：保存数据修改前的版本，用于事务回滚（保证 atomicity）和 MVCC 快照读。
◦ 注意：undo log 会被 purge 线程定期清理（当没有事务需要旧版本时）。

3.2.1.5 终极心法：事务设计的 “3 个凡是”

1. 凡是不需要事务的操作，坚决不用 （如日志插入可关闭自动提交，批量提交）。
2. 凡是能在 RC 解决的，绝不升 RR （互联网业务优先选 RC，用业务逻辑防不可重复读）。
3. 凡是大事务，必拆分成 “读 – 算 – 写” 三步 （读阶段不加锁，算阶段在应用层，写阶段用最短事务加锁）。

记住：事务的本质不是 “约束”，而是 “工具”—— 能解决问题的最简单事务，才是最好的事务。



3.2.2 理论应用实践

3.2.2.1 本次事故的物料：

表的事务等级：

<span><span><span>SELECT @@transaction_isolation;

</span></span></span>



 

 



需要删除的指标实现(根据实现id):

<span><span><span>delete from unify_metric_impl where logic_table_id in (45631);

</span></span></span>

需要插入的指标实现：

<span><span><span>INSERT INTO `unify_metric_impl` (`id`, `metric_def_id`, `logic_table_id`, `name_en_atomic`, `committer`, `create_time`, `update_time`, `metric_atomic_name_temp`, `decorate_id_list_temp`, `name_cn_alias_temp`, `metric_type_temp`, `description_temp`, `data_type_temp`, `data_accuracy_temp`, `security_level_temp`, `logic_table_id_excel_temp`, `implement_type`, `app_ori_metric_name_temp`, `name_en_depend_atomic`, `name_en_depend_app`, `update_status`, `status`, `light_decorate_id_list`, `extend_decorate_id_list`, `extend_function_id_list`, `aggregation_type`, `middle_aggregation_type`, `static_decorate_id_list_combination`)
VALUES
    (1358195, 19872, 45631, 'jdr_7fresh_trade_complete_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358196, 19873, 45631, 'jdr_7fresh_trade_complete_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358197, 19875, 45631, 'jdr_7fresh_trade_complete_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358198, 19945, 45631, 'jdr_7fresh_sku_deal_ord_sku_dis_qtty_main_img_video_num', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358199, 17263, 45631, 'jdr_7fresh_trade_deal_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358200, 28017, 45631, 'jdr_7fresh_bd_bd_attendance_offline__store_cnt_bd_attendance_cnt', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'COUNT_DISTINCT', 'DEFAULT', ''),
    (1358201, 20242, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358202, 18450, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358203, 20276, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_cnt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358204, 18452, 45631, 'jdr_7fresh_trade_deal_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358205, 18453, 45631, 'jdr_7fresh_trade_deal_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358206, 18456, 45631, 'jdr_7fresh_trade_deal_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358207, 19866, 45631, 'jdr_7fresh_trade_complete_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358208, 21691, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_include_moutai', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (1358209, 19871, 45631, 'jdr_7fresh_trade_complete_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', '');

</span></span></span>

3.2.2.2 实践分析：

用sql模拟两个事务的执行过程：

事务1：

<span><span><span>begin;

select * from unify_metric_impl umi where logic_table_id =  45631;

delete from unify_metric_impl where logic_table_id in (45631);

SELECT trx_id, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX;

select * from unify_metric_impl umi where logic_table_id =  45631;

INSERT INTO `unify_metric_impl` ( `metric_def_id`, `logic_table_id`, `name_en_atomic`, `committer`, `create_time`, `update_time`, `metric_atomic_name_temp`, `decorate_id_list_temp`, `name_cn_alias_temp`, `metric_type_temp`, `description_temp`, `data_type_temp`, `data_accuracy_temp`, `security_level_temp`, `logic_table_id_excel_temp`, `implement_type`, `app_ori_metric_name_temp`, `name_en_depend_atomic`, `name_en_depend_app`, `update_status`, `status`, `light_decorate_id_list`, `extend_decorate_id_list`, `extend_function_id_list`, `aggregation_type`, `middle_aggregation_type`, `static_decorate_id_list_combination`)
VALUES
    (19872, 45631, 'jdr_7fresh_trade_complete_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (19873, 45631, 'jdr_7fresh_trade_complete_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (19875, 45631, 'jdr_7fresh_trade_complete_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (19945, 45631, 'jdr_7fresh_sku_deal_ord_sku_dis_qtty_main_img_video_num', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (17263, 45631, 'jdr_7fresh_trade_deal_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (28017, 45631, 'jdr_7fresh_bd_bd_attendance_offline__store_cnt_bd_attendance_cnt', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'COUNT_DISTINCT', 'DEFAULT', ''),
    (20242, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (18450, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (20276, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_cnt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (18452, 45631, 'jdr_7fresh_trade_deal_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (18453, 45631, 'jdr_7fresh_trade_deal_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (18456, 45631, 'jdr_7fresh_trade_deal_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (19866, 45631, 'jdr_7fresh_trade_complete_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (21691, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_include_moutai', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''),
    (19871, 45631, 'jdr_7fresh_trade_complete_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', '');

commit;

</span></span></span>



事务2：

<span><span><span>begin;

select * from unify_metric_impl umi where logic_table_id =  45631;

delete from unify_metric_impl where logic_table_id in (45631);

select * from unify_metric_impl umi where logic_table_id =  45631;

commit;

</span></span></span>



流程图(用一行数据进行演示版本控制)：



 

 



为何事务1的select查询出“为空”，事务2的select查询出“不为空”：

对比维度 事务 1 查询（读自己的删除版本 V2） 事务 2 查询（读readview前的V1版本）
自己生成的版本 V2（trx_id=17190，已删除） V4（trx_id=17191，已删除）
对自己版本的处理 可见，且 事务内需反映自己的删除操作 ，所以不追溯前驱 V1 不可见，但 当前删除的版本是由其他事务得到(V3) ，并非在readview之前的数据。
追溯的终止条件 遇到自己生成的版本，即使已删除，也终止追溯 遇到自己生成的已删除版本，但不符合”有效删除”，需继续追溯
最终返回结果 v2（已删除版本，反映自己的删除操作） V1（readview之前有效的版本）



4.解决办法

为了解决事务2的查询”不为空”的问题，分别列出以下方案：

 解决办法 优点 缺点 倾向
方式1 针对同一个逻辑表的同步添加分布式锁 实现成本低，影响范围小 存在长事务的问题 短期解法
方式2 将事务2的select改为当前读(使用slecet…for update)，这样就能查询出最新的数据为空 实现成本低， 存在长事务的问题，影响范围大(长事务涉及逻辑多) 不推荐
方式3 将长事务拆分， “读 – 算 – 写” 三步 1. 读：无锁读取原子服务与实现数据； 2. 算：在应用层对比新增 / 删除数据； 3. 写：仅对差异数据执行短事务操作 从根源解决问题 实现成本大，重构该方法 长期解法

当前落地情况 ：已通过 “分布式锁控制同一逻辑表同步并发” 的短期方案解决事故，后续将在业务迭代中推进 “读 – 算 – 写” 拆分的长期优化，进一步降低事务粒度与锁冲突风险。

5.附录

5.1名词解释

事实逻辑表：由物理数仓中的事实表和维度逻辑表关联形成的语义表，可以描述业务过程的详细信息，是指标的数据来源。

原子服务：指标的实现方式，一个指标可以有多个实现。



                                                                                </div>



Source link

未经允许不得转载：紫竹林-程序员中文网 » 并发丢数据深度剖析：MySQL锁机制与事务实战踩坑及解决方案

相关推荐

评论 抢沙发

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

近期文章

关于我们 免责申明 意见反馈 隐私政策
程序员中文网：公益在线网站，帮助学习者快速成长！
关注微信 技术交流
推荐文章
每天精选资源文章推送
推荐文章
随时随地碎片化学习
推荐文章
发现有趣的

Copyright©2010-2022 鄂ICP备16010909号